/*
* Conditions Of Use
*
* This software was developed by employees of the Sigmatrix(Beijing) Corporation.
* This software is provided by sigmatrix as a service and is expressly
* provided "AS IS."  Sigmatrix MAKES NO WARRANTY OF ANY KIND, EXPRESS, IMPLIED
* OR STATUTORY, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTY OF
* MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, NON-INFRINGEMENT
* AND DATA ACCURACY.  Sigmatrix does not warrant or make any representations
* regarding the use of the software or the results thereof, including but
* not limited to the correctness, accuracy, reliability or usefulness of
* the software.
*
* Permission to use this software is contingent upon your acceptance
* of the terms of this agreement.
*
*/
package com.sigmatrix.h5.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSONObject;
import com.sigmatrix.h5.dao.InStorageDetailDao;
import com.sigmatrix.h5.dao.impl.common.impl.BaseDaoImpl;
import com.sigmatrix.h5.dto.InStorageDetailData;
import com.sigmatrix.h5.entity.InStorageDetail;
import com.sigmatrix.h5.enums.InStorageStatusEnum;
import com.sigmatrix.h5.exception.DaoException;
import com.sigmatrix.h5.utils.Cons;
import com.sigmatrix.h5.utils.Utils;

/**
 * @ClassName: InStorageDetailDaoImpl
 * @Description: 入库单详情数据库操作接口实现
 * @author youjun 
 * @date 2017-07-06 14:21:21
 */
@Repository(value = "inStorageDetailDao")
public class InStorageDetailDaoImpl extends BaseDaoImpl<InStorageDetail> implements InStorageDetailDao {

	private static final String TABLE_NAME = "_dealers_in_storage_detail";
	private static final Logger LOGGER = LoggerFactory.getLogger(InStorageDetailDaoImpl.class);

	/**
	 * (non-Javadoc)
	 *@Title: findById
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.InStorageDetailDao#findById(java.lang.Integer, java.lang.String)
	 *@author youjun
	 *@date 2017年7月15日 下午2:15:16
	 */
	@Override
	public InStorageDetail findById(final Long id, final String eseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder()
				.append("SELECT * FROM ")
				.append(getTname(eseCode, TABLE_NAME))
				.append(" WHERE _id = ").append(id);
		final List<InStorageDetail> list = jdbcTemplate.query(sql.toString(),new InStorageDetailRowMapper());
		if (Utils.isEmpty(list)) {
			return null;
		}else{
			return list.get(0);
		}
	}
	
	/**
	 * (non-Javadoc)
	 *@Title: findByInStorageId
	 *@Description: 
	 *@see com.sigmatrix.h5.dao.InStorageDetailDao#findByInStorageId(java.lang.Integer, java.lang.String)
	 *@author 
	 *@date 2017年9月11日 下午6:04:39
	 */
	public List<InStorageDetail> findByInStorageId(final Long inStorageId, final String eseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder()
				.append("SELECT * FROM ")
				.append(getTname(eseCode, TABLE_NAME))
				.append(" WHERE _in_storage_id = ").append(inStorageId);
		return jdbcTemplate.query(sql.toString(),new InStorageDetailRowMapper());
	}
	
	/**(non-Javadoc)
	 * @throws DaoException 
	 *@Title: batchSave
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.InStorageDetailDao#batchSave(java.util.List)
	 *@author youjun
	 *@date 2017年7月6日 下午3:56:25
	 */
	@Override
	public int batchSave(final List<InStorageDetail> detailList, final String eseCode) throws DaoException {

		if(Utils.isEmpty(detailList)) {
			throw new DaoException("批量保存明细");
		}
		final List<Object[]> paramList = new ArrayList<Object[]>();
		final StringBuilder sql = new StringBuilder()
				.append("INSERT INTO ")
				.append(getTname(eseCode, TABLE_NAME))
				.append("( _in_storage_id,_order_code,_product_id,_product_code,")
				.append("_plan_num,_code_level,_wrap_name,_creator_id,")
				.append("_create_time,_status,_actual_num,_operate_content)")
				.append("VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?)");
		for(final InStorageDetail detail:detailList) {
			final Object[] params = new Object[]{
					detail.getInStorageId(),detail.getOrderCode(),detail.getProductId(),detail.getProductCode(),
					detail.getPlanNum(),detail.getCodeLevel(),detail.getWrapName(),detail.getCreatorId(),
					detail.getCreateTime(),detail.getStatus(),detail.getActualNum(),detail.getOperateContent()
					};
			paramList.add(params);
		}
		final int[] results = jdbcTemplate.batchUpdate(sql.toString(), paramList);
		return results[0];
	}
	
	/**
	 * (non-Javadoc)
	 *@Title: updateById
	 *@Description: 
	 *@see com.sigmatrix.h5.dao.InStorageDetailDao#updateById(java.lang.String, java.lang.Long, java.lang.String, java.lang.Integer)
	 *@date 2017年9月27日 上午9:54:06
	 */
	public int updateById(final String eseCode,final Long detailId,final String operCont,final Integer actualNum,final Integer status) throws DaoException{
		final StringBuilder sql = new StringBuilder()
				.append("UPDATE ")
				.append(getTname(eseCode, TABLE_NAME))
				.append(" SET _actual_num = ?,_operate_content = ?,_status = ? ")
				.append(" WHERE _id = ?");
		final Object[] params = new Object[]{actualNum,operCont,status,detailId};
		final int[] argTypes = new int[]{Types.INTEGER,Types.VARCHAR,Types.SMALLINT,Types.BIGINT};
		LOGGER.info(sql.toString());
		LOGGER.info(JSONObject.toJSONString(params));
		return jdbcTemplate.update(sql.toString(), params, argTypes);
	}
	

	@Override
	public int updateById(String eseCode, Long detailId, String operCont, Integer actualNum, Integer status,
			Integer planNum) throws DaoException {
		final StringBuilder sql = new StringBuilder()
				.append("UPDATE ")
				.append(getTname(eseCode, TABLE_NAME))
				.append(" SET _actual_num = ?,_operate_content = ?,_status = ?,_plan_num = ?")
				.append(" WHERE _id = ?");
		final Object[] params = new Object[]{actualNum,operCont,status,planNum,detailId};
		final int[] argTypes = new int[]{Types.INTEGER,Types.VARCHAR,Types.SMALLINT,Types.INTEGER,Types.BIGINT};
		LOGGER.info(sql.toString());
		LOGGER.info(JSONObject.toJSONString(params));
		return jdbcTemplate.update(sql.toString(), params, argTypes);
	}

	
	/**
	 * (non-Javadoc)
	 *@Title: sumActualNumByLevel
	 *@Description: 
	 *@see com.sigmatrix.h5.dao.InStorageDetailDao#sumActualNumByLevel(java.lang.String, java.lang.Long, java.lang.Integer)
	 *@author 
	 *@date 2017年9月27日 下午5:17:29
	 */
	public int sumActualNumByLevel(final String eseCode,final Long inStorageId,final Integer level) throws DaoException{
		if(Utils.isEmpty(inStorageId)){
			throw new DaoException("请求参数不可以为空 inStorageId");
		}
		final StringBuilder sql = new StringBuilder()
				.append("SELECT * FROM ")
				.append(getTname(eseCode, TABLE_NAME))
				.append(" WHERE ")
				.append(" _in_storage_id = ").append(inStorageId);
		if(Utils.isNotEmpty(level)){
			sql.append(" AND _code_level = ").append(level);
		}
		LOGGER.info(sql.toString());
		int num = 0;
		List<InStorageDetail> list = jdbcTemplate.query(sql.toString(),new InStorageDetailRowMapper());
		for(int i=0 ; i<list.size() ; i++){
			InStorageDetail inStorageDetail = list.get(i);
			LOGGER.info("查询入库单明细单条信息循环="+JSONObject.toJSONString(inStorageDetail));
			if (Utils.isEmpty(inStorageDetail.getActualNum())) {
				LOGGER.info("查询入库单明细单条信息_actual_num为空");
				continue;
			}
			else {
				num = num+inStorageDetail.getActualNum();
			}
		}
		return num;
	}
	
	/**(non-Javadoc)
	 *@Title: closeByInStorageId
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.InStorageDetailDao#closeByInStorageId(int, java.util.List, java.lang.String)
	 *@author youjun
	 *@date 2017年8月23日 下午5:21:11
	 */
	@Override
	public int closeByInStorageId(Long inStorageId, List<Integer> canCloseStatusList, String eseCode)
			throws DaoException {

		if(Utils.isEmpty(eseCode)||Utils.isEmpty(canCloseStatusList)) {
			throw new DaoException("关闭入库单详情参数有误");
		}
		StringBuilder sql = new StringBuilder("UPDATE ")
				.append(getTname(eseCode, TABLE_NAME))
				.append(" SET _status = ? WHERE _in_storage_id = ? AND _status IN (");
		final List<Object> paramList = new ArrayList<Object>();
		paramList.add(InStorageStatusEnum.CLOSE.getId());
		paramList.add(inStorageId);
		for(final Integer canStauts:canCloseStatusList) {
			sql.append("?").append(",");
			paramList.add(canStauts);
		}
		sql = new StringBuilder(sql.substring(0, sql.length()-1)); // 去除最后的","
		sql.append(")");
		return jdbcTemplate.update(sql.toString(), paramList.toArray());
	}
	
	@Override
	public List<InStorageDetail> findByPage(final int pageNo, final int pageSize, 
			final InStorageDetail inStorageDetail, final String enterpriseCode)
			throws DaoException {

		final StringBuilder sql = new StringBuilder("SELECT * FROM ");
		final int startNo = (pageNo - 1) * pageSize + 1; // 当前起始条数
		final int endNo = pageNo * pageSize; // 当前结束条数
		final List<Object> paramList = new ArrayList<Object>();
		sql.append("(SELECT *, ROW_NUMBER() OVER(ORDER BY a._create_time DESC) AS RowNumber FROM ")
		.append(getTname(enterpriseCode, TABLE_NAME)).append(" as a ");
		sql.append("WHERE a._status <> ?");
		paramList.add(Cons.STATUS_DELETE);
		if (inStorageDetail != null) {
			if (inStorageDetail.getInStorageId() != null) {
				sql.append(" AND a._in_storage_id = ?");
				paramList.add(inStorageDetail.getInStorageId());
			}
			if (inStorageDetail.getProductId() != null) {
				sql.append(" AND a._product_id = ?");
				paramList.add(inStorageDetail.getProductId());
			}
//			if (StringUtils.isNotBlank(inStorageDetail.getWorkId())) {
//				sql.append(" AND a._work_id = ?");
//				paramList.add(inStorageDetail.getWorkId());
//			}
		}
		sql.append(") as b WHERE RowNumber BETWEEN ? AND ?");
		paramList.add(startNo);
		paramList.add(endNo);
		final Object[] params = paramList.toArray();
		try {
			return jdbcTemplate.query(sql.toString(), params, new InStorageDetailRowMapper());
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}

	@Override
	public int getCountByCondition(final InStorageDetail inStorageDetail, final String enterpriseCode) throws DaoException {

		final List<Object> paramList = new ArrayList<Object>();
		final StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM ").append(getTname(enterpriseCode, TABLE_NAME));
		sql.append(" WHERE _status <> ?");
		paramList.add(Cons.STATUS_DELETE);
		try {
			if (inStorageDetail != null) {
				if (inStorageDetail.getInStorageId() != null) {
					sql.append(" AND _in_storage_id = ?");
					paramList.add(inStorageDetail.getInStorageId());
				}
				if (inStorageDetail.getProductId() != null) {
					sql.append(" AND _product_id = ?");
					paramList.add(inStorageDetail.getProductId());
				}
//				if (StringUtils.isNotBlank(inStorageDetail.getWorkId())) {
//					sql.append(" AND _work_id = ?");
//					paramList.add(inStorageDetail.getWorkId());
//				}
			}
			return jdbcTemplate.queryForObject(sql.toString(), paramList.toArray(), Integer.class);
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}

	@Override
	public List<InStorageDetail> findListByCondition(final InStorageDetail inStorageDetail,
			final String enterpriseCode) throws DaoException {

		final StringBuilder sql = new StringBuilder()
				.append("SELECT * FROM ")
				.append(getTname(enterpriseCode, TABLE_NAME));
		final List<Object> paramList = new ArrayList<Object>();
		sql.append(" WHERE _status <> ?");
		paramList.add(Cons.STATUS_DELETE);
		try {
			// 请不要使用like
			if(inStorageDetail!=null) {
				if(inStorageDetail.getInStorageId()!=null){
					sql.append(" AND _in_storage_id = ?");
					paramList.add(inStorageDetail.getInStorageId());
				}
				if(inStorageDetail.getProductId()!=null){
					sql.append(" AND _product_id = ?");
					paramList.add(inStorageDetail.getProductId());
				}
			}
			return jdbcTemplate.query(sql.toString(), paramList.toArray(), new InStorageDetailRowMapper());
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}
	
	/**(non-Javadoc)
	 *@Title: findPartColumnListByCondition
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.InStorageDetailDao#findPartColumnListByCondition(com.sigmatrix.sc.BigDataInStorageDetail.inter.entity.InStorageDetail)
	 *@author youjun
	 *@date 2017年7月7日 下午9:30:31
	 */
	@Override
	public List<InStorageDetailData> findPartColumnListByCondition111(InStorageDetail inStorageDetail,
			final String enterpriseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder()
				.append("SELECT _id,_produce_batch_id,_in_storage_id,_product_id")
				.append(",_product_code,_status,_operate_code_content,_success_code_content,_plan_num,_plan_bottle,_actual_num,_actual_bottle")
				.append(" FROM ").append(getTname(enterpriseCode, TABLE_NAME));
		final List<Object> paramList = new ArrayList<Object>();
		sql.append(" WHERE _status <> ?");
		paramList.add(Cons.STATUS_DELETE);
		try {
			return jdbcTemplate.query(sql.toString(), paramList.toArray(), new RowMapper<InStorageDetailData>(){
				@Override
				public InStorageDetailData mapRow(final ResultSet resultSet, final int rowNum) throws SQLException {
					final InStorageDetailData inStorageDetail = new InStorageDetailData();
//					inStorageDetail.setDetailId(resultSet.getInt("_id"));
//					inStorageDetail.setProduceBatchId(resultSet.getString("_produce_batch_id"));
//					inStorageDetail.setInStorageId(resultSet.getInt("_in_storage_id"));
					inStorageDetail.setProductId(resultSet.getInt("_product_id"));
					inStorageDetail.setProductCode(resultSet.getString("_product_code"));
//					inStorageDetail.setStatus(resultSet.getShort("_status"));
//					inStorageDetail.setOperateCodeContent(resultSet.getString("_operate_code_content"));
//					inStorageDetail.setSuccessCodeContent(resultSet.getString("_success_code_content"));
					inStorageDetail.setPlanNum(resultSet.getInt("_plan_num"));
//					inStorageDetail.setPlanBottle(resultSet.getInt("_plan_bottle"));
					inStorageDetail.setActualNum(resultSet.getInt("_actual_num"));
//					inStorageDetail.setActualBottle(resultSet.getInt("_actual_bottle"));
					return inStorageDetail;
				}
			});
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}
	
	
	/**(non-Javadoc)
	 *@Title: findByCondition
	 *@Description: 
	 *@see com.sigmatrix.h5.dao.InStorageDetailDao#findByCondition(java.lang.String, java.lang.Long, java.lang.Integer, java.lang.Integer)
	 *@author 
	 *@date 2017年9月22日 下午5:32:43
	 */
	@Override
	public List<InStorageDetail> findByCondition(final String eseCode,final Long inStorageId,
			final Integer productId,final Integer level) throws DaoException {
		final StringBuilder sql = new StringBuilder()
		.append("SELECT * FROM ")
		.append(getTname(eseCode, TABLE_NAME))
		.append(" WHERE _in_storage_id = ").append(inStorageId)
		.append(" AND _product_id = ").append(productId)
		.append(" AND _code_level = ").append(level);
		LOGGER.info(sql.toString());
		return jdbcTemplate.query(sql.toString(),new InStorageDetailRowMapper());
	}
	
	/**
	 * @ClassName: InStorageDetailRowMapper
	 * @Description: 封装返回的实体
	 * @author youjun 
	 * @date 2017-07-06 14:21:21
	 */
	public class InStorageDetailRowMapper implements RowMapper<InStorageDetail> {
		public InStorageDetail mapRow(final ResultSet rs, final int rowNum) throws SQLException {
			final InStorageDetail obj = new InStorageDetail();
			obj.setId(rs.getLong("_id"));
			obj.setInStorageId(rs.getLong("_in_storage_id"));
			obj.setOrderCode(rs.getString("_order_code"));
			obj.setProductId(rs.getInt("_product_id"));
			obj.setProductCode(rs.getString("_product_code"));
			obj.setStockId(rs.getLong("_stock_id"));
			obj.setCreatorId(rs.getInt("_creator_id"));
			obj.setCreateTime(rs.getTimestamp("_create_time"));
			obj.setActualNum(rs.getInt("_actual_num"));
			obj.setPlanNum(rs.getInt("_plan_num"));
			obj.setCodeLevel(rs.getInt("_code_level"));
			obj.setWrapName(rs.getString("_wrap_name"));
			obj.setOperateContent(rs.getString("_operate_content"));
			obj.setIsConfirm(rs.getShort("_is_confirm"));
			obj.setPlanSpNum(rs.getInt("_plan_sp_num"));
			obj.setActualSpNum(rs.getInt("_actual_sp_num"));
			obj.setStatus(rs.getShort("_status"));
	        return obj; 
		}
	}


}
